package com.example.student_manager_backend.util;

import com.example.student_manager_backend.entity.Student;
import com.example.student_manager_backend.status.StudentStatus;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * Excel工具类
 * 用于处理Excel文件的导入导出操作，支持.xlsx格式
 */
public class ExcelUtil {

    private static final String[] HEADERS = {
            "学号", "姓名", "性别", "生日", "班级", "电话", "邮箱", "状态"
    };

    /**
     * 从Excel文件中读取数据
     * 将Excel文件的内容转换为List<String[]>格式
     *
     * @param file Excel文件
     * @return 包含Excel数据的列表，每个元素是一个字符串数组，代表一行数据
     * @throws IOException 如果文件读取失败
     */
    public static List<String[]> readExcel(MultipartFile file) throws IOException {
        List<String[]> dataList = new ArrayList<>();
        
        try (InputStream is = file.getInputStream();
             Workbook workbook = new XSSFWorkbook(is)) {
            
            Sheet sheet = workbook.getSheetAt(0);
            for (Row row : sheet) {
                List<String> rowData = new ArrayList<>();
                for (Cell cell : row) {
                    rowData.add(getCellValueAsString(cell));
                }
                dataList.add(rowData.toArray(new String[0]));
            }
        }
        
        return dataList;
    }

    /**
     * 将数据写入Excel文件
     * 创建一个新的Excel文件并写入数据
     *
     * @param headers 表头数组
     * @param dataList 数据列表，每个元素是一个字符串数组，代表一行数据
     * @return 包含Excel数据的字节数组
     * @throws IOException 如果文件写入失败
     */
    public static byte[] writeExcel(String[] headers, List<String[]> dataList) throws IOException {
        try (Workbook workbook = new XSSFWorkbook();
             ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) {
            
            Sheet sheet = workbook.createSheet();
            
            // 创建表头
            Row headerRow = sheet.createRow(0);
            for (int i = 0; i < headers.length; i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(headers[i]);
            }
            
            // 写入数据
            for (int i = 0; i < dataList.size(); i++) {
                Row row = sheet.createRow(i + 1);
                String[] rowData = dataList.get(i);
                for (int j = 0; j < rowData.length; j++) {
                    Cell cell = row.createCell(j);
                    cell.setCellValue(rowData[j]);
                }
            }
            
            // 自动调整列宽
            for (int i = 0; i < headers.length; i++) {
                sheet.autoSizeColumn(i);
            }
            
            // 将工作簿写入输出流
            workbook.write(outputStream);
            return outputStream.toByteArray();
        }
    }

    /**
     * 获取单元格的值并转换为字符串
     * 支持不同类型的单元格数据
     *
     * @param cell Excel单元格
     * @return 单元格值的字符串表示
     */
    private static String getCellValueAsString(Cell cell) {
        if (cell == null) {
            return "";
        }
        
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                }
                return String.valueOf(cell.getNumericCellValue());
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return "";
        }
    }

    public static List<Student> importStudentsFromExcel(MultipartFile file) throws IOException {
        List<Student> students = new ArrayList<>();
        Workbook workbook = WorkbookFactory.create(file.getInputStream());
        Sheet sheet = workbook.getSheetAt(0);

        // Skip header row
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (row == null) continue;

            Student student = new Student();
            student.setStudentId(getCellValueAsString(row.getCell(0)));
            student.setName(getCellValueAsString(row.getCell(1)));
            student.setGender(getCellValueAsString(row.getCell(2)));
            student.setBirthday(row.getCell(3).getDateCellValue());
            student.setClassId(getCellValueAsString(row.getCell(4)));
            student.setPhone(getCellValueAsString(row.getCell(5)));
            student.setEmail(getCellValueAsString(row.getCell(6)));
            student.setStatus(StudentStatus.valueOf(getCellValueAsString(row.getCell(7))));
            student.setCreateTime(new Date());
            student.setUpdateTime(new Date());

            students.add(student);
        }

        workbook.close();
        return students;
    }

    public static byte[] exportStudentsToExcel(List<Student> students) throws IOException {
        try (Workbook workbook = new XSSFWorkbook();
             ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) {
            
            Sheet sheet = workbook.createSheet("Students");

            // Create header row
            Row headerRow = sheet.createRow(0);
            for (int i = 0; i < HEADERS.length; i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(HEADERS[i]);
            }

            // Create data rows
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
            for (int i = 0; i < students.size(); i++) {
                Student student = students.get(i);
                Row row = sheet.createRow(i + 1);

                row.createCell(0).setCellValue(student.getStudentId());
                row.createCell(1).setCellValue(student.getName());
                row.createCell(2).setCellValue(student.getGender());
                row.createCell(3).setCellValue(dateFormat.format(student.getBirthday()));
                row.createCell(4).setCellValue(student.getClassId());
                row.createCell(5).setCellValue(student.getPhone());
                row.createCell(6).setCellValue(student.getEmail());
                row.createCell(7).setCellValue(student.getStatus().name());
            }

            // Auto-size columns
            for (int i = 0; i < HEADERS.length; i++) {
                sheet.autoSizeColumn(i);
            }

            // Write to byte array
            workbook.write(outputStream);
            return outputStream.toByteArray();
        }
    }
} 